Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Keeping Records of Current and Backup Database Files

One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:

- Datafiles and control files

- Online and archived redo logs (note that online logs are never backed up)

- Initialization parameter files

- Password files

- Networking-related files

## Recording the Locations of Datafiles, Control Files, and Online Redo Logs

The following useful SQL script displays the location of all control files, datafiles, and online redo log files for the database:

    SELECT NAME FROM V$DATAFILE
    UNION ALL
    SELECT MEMBER FROM V$LOGFILE
    UNION ALL
    SELECT NAME FROM V$CONTROLFILE;

See Also: Oracle Database Reference for more information on the `V$` views

## Recording the Locations of Archived Redo Logs

You can determine the location of the default archived log destinations by executing the following SQL script:

    SELECT NAME, VALUE
    FROM V$PARAMETER
    WHERE NAME LIKE log_archive_dest%
    AND VALUE IS NOT NULL;

    NAME                               VALUE
    ---------------------------------- -------------------------------------------
    log_archive_dest_1                 LOCATION=/oracle/work/arc_dest/arc
    log_archive_dest_state_1           enable

Determine the format for archived logs by running `SHOW` as follows:

    SHOW PARAMETER LOG_ARCHIVE_FORMAT

To see a list of all the archived logs recorded in the control file, issue this query:

    SELECT NAME FROM V$ARCHIVED_LOG;

## Recording the Locations and Dates of Backup Files

It is not enough to merely record the location of backup files: you must correlate the backups with the original files. If possible, name the backups with names derived from the name of the primary file. Whatever naming system you use, keep a table containing the relevant information. For example, you could keep the following table as a record of database file locations in case of a restore emergency.

Datafile # | Tbs | Current Datafiles | Backup Datafiles
--- | --- | --- | ---
`0` (cf) | `0` (cf) | `/oracle/oradata/trgt/control01.dbf` | `/d2/control01_10_31_02.dbf`
`1` | `SYSTEM` | `/oracle/oradata/trgt/system01.dbf` | `/d2/system01_10_31_02.dbf`
`2` | `undo` | `/oracle/oradata/trgt/undo01.dbf` | `/d2/undo01_10_31_02.dbf`
`3` | `cwmlite` | `/oracle/oradata/trgt/cwmlite01.dbf` | `/d2/cwmlite01_10_31_02.dbf`
`4` | `drsys` | `/oracle/oradata/trgt/drsys01.dbf` | `/d2/drsys01_10_31_02.dbf`